﻿// --------------------------------------------------------------------------------------------------------------------
// <copyright file="SqlUnspscRepository.cs" company="wwwlicious">
//   All rights reserved 2009.
// </copyright>
// <summary>
//   Contains the SqlUnspscRepository class.
// </summary>
// --------------------------------------------------------------------------------------------------------------------

namespace Unspsc.Data
{
    using System.Linq;

    using Audit;

    using Unspsc.Data.DataAccess.Sql;

    /// <summary>
    /// An instance of the excel unspsc repository
    /// </summary>
    public class SqlUnspscRepository : IUnspscRepository
    {
        /// <summary>
        /// An instance of the excel provider
        /// </summary>
        private readonly UnspscDataContext provider;

        /// <summary>
        /// Initializes a new instance of the <see cref="SqlUnspscRepository"/> class.
        /// </summary>
        /// <param name="unspscConnectionString">The unspsc connection string.</param>
        public SqlUnspscRepository(string unspscConnectionString)
        {
            provider = new UnspscDataContext(unspscConnectionString) {Log = new DebuggerWriter() };
        }

        #region IUnspscRepository Members

        /// <summary>
        /// Gets the available versions.
        /// </summary>
        /// <returns>IQueryable of version numbers</returns>
        public IQueryable<double> GetVersions()
        {
            return provider.Segments.Select(s => (double)s.Version).Distinct().OrderByDescending(s => s);
        }

        /// <summary>
        /// Gets the segments.
        /// </summary>
        /// <param name="version">The version.</param>
        /// <returns>IQueryable of IUnspscElement</returns>
        public IQueryable<IUnspscElement> GetSegments(double version)
        {
            return provider.Segments.WithVersion(version).Select(s => s.SqlToPoco());
        }

        /// <summary>
        /// Gets the families.
        /// </summary>
        /// <param name="version">The version.</param>
        /// <returns>IQueryable of families</returns>
        public IQueryable<IUnspscElement> GetFamilies(double version)
        {
            return provider.Families.WithVersion(version).Select(s => s.SqlToPoco());
        }

        /// <summary>
        /// Gets the families.
        /// </summary>
        /// <param name="segmentCode">The segment code.</param>
        /// <param name="version">The version.</param>
        /// <returns>IQueryable of IUnspscElement</returns>
        public IQueryable<IUnspscElement> GetFamilies(UnspscCode segmentCode, double version)
        {
            UnspscCode segmentEnd = segmentCode.Segment + 1000000d;
            return provider.Families.WithVersion(version).Where(s => s.Code > segmentCode && s.Code < segmentEnd).Select(x => x.SqlToPoco());
        }

        /// <summary>
        /// Gets the classes.
        /// </summary>
        /// <param name="version">The version.</param>
        /// <returns>IQueryable of classes</returns>
        public IQueryable<IUnspscElement> GetClasses(double version)
        {
            return provider.Classes.WithVersion(version).Select(s => s.SqlToPoco());
        }

        /// <summary>
        /// Gets the classes.
        /// </summary>
        /// <param name="familyCode">The family code.</param>
        /// <param name="version">The version.</param>
        /// <returns>IQueryable of IUnspscElement</returns>
        public IQueryable<IUnspscElement> GetClasses(UnspscCode familyCode, double version)
        {
            UnspscCode familyEnd = familyCode + 100000d;
            return provider.Classes.WithVersion(version).Where(f => f.Code > familyCode && f.Code < familyEnd).Select(x => x.SqlToPoco());
        }

        /// <summary>
        /// Gets the commodities.
        /// </summary>
        /// <param name="version">The version.</param>
        /// <returns>IQueryable of commodities</returns>
        public IQueryable<IUnspscElement> GetCommodities(double version)
        {
            return provider.Commodities.WithVersion(version).Select(s => s.SqlToPoco());
        }

        /// <summary>
        /// Gets the classes.
        /// </summary>
        /// <param name="classCode">The class code.</param>
        /// <param name="version">The version.</param>
        /// <returns>IQueryable of IUnspscElement</returns>
        public IQueryable<IUnspscElement> GetCommodities(UnspscCode classCode, double version)
        {
            return provider.Commodities.WithVersion(version).Where(x => x.ClassCode == classCode).Select(s => s.SqlToPoco());
        }

        /// <summary>
        /// Gets a IUnspscElement.
        /// </summary>
        /// <param name="unspscCode">The unspsc code.</param>
        /// <param name="version">The version.</param>
        /// <returns>IUnspscElement object</returns>
        public IUnspscElement GetUnspscElement(UnspscCode unspscCode, double version)
        {
            if (unspscCode.IsSegment)
                return provider.Segments.WithKey(unspscCode.Segment, version).SqlToPoco();

            if (unspscCode.IsFamily)
                return provider.Families.WithKey(unspscCode.Family, version).SqlToPoco();

            if (unspscCode.IsClass)
                return provider.Classes.WithKey(unspscCode.Class, version).SqlToPoco();

            if (unspscCode.IsCommodity)
                return provider.Commodities.WithKey(unspscCode.Commodity, version).SqlToPoco();

            return null;
        }

        /// <summary>
        /// Gets the audit log.
        /// </summary>
        /// <param name="code">The unspsc code.</param>
        /// <returns>IQueryable of IAuditItem</returns>
        public IQueryable<IAuditItem> GetAuditLog(UnspscCode code)
        {
            return provider.AuditLogs.Where(
                            x => x.effective_code == code || x.changed_code == code).OrderBy(x => x.effective_version).Select(s => s.SqlToPoco());
        }

        /// <summary>
        /// Searches commodity titles and descriptions
        /// </summary>
        /// <param name="keywords">The keywords.</param>
        /// <param name="version">The version.</param>
        /// <returns>IQueryable of IUnspscElement</returns>
        public IQueryable<IUnspscElement> SearchText(string[] keywords, double version)
        {
            var predicate = PredicateBuilder.False<Commodity>();
            foreach (string word in keywords)
            {
                string temp = word;
                predicate =
                        predicate.Or(
                                s =>
                                s.CommodityTitle.Contains(temp) || s.ClassTitle.Contains(temp)
                                || s.Definition.Contains(temp));
            }

            return provider.Commodities.WithVersion(version).Where(predicate).Select(s => s.SqlToPoco());
        }

        #endregion
    }
}